package com.example.online_exam_system.dao;

import com.example.online_exam_system.entity.StuExam;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * (StuExam)表数据库访问层
 *
 * @author lianglong
 * @since 2022-03-20 10:54:38
 */
public interface StuExamDao {

    /**
     * 通过ID查询单条数据
     *
     * @param  主键
     * @return 实例对象
     */
    StuExam queryById( );

    /**
     * 查询指定行数据
     *
     * @param offset 查询起始位置
     * @param limit 查询条数
     * @return 对象列表
     */
    List<StuExam> queryAllByLimit(@Param("offset") int offset, @Param("limit") int limit);


    /**
     * 通过实体作为筛选条件查询
     *
     * @param stuExam 实例对象
     * @return 对象列表
     */
    List<StuExam> queryAll(StuExam stuExam);

    /**
     * 新增数据
     *
     * @param stuExam 实例对象
     * @return 影响行数
     */
    @Insert(" insert into exam_system.stu_exam(testid, userid, reply, remark, questionid, score)\n" +
            "        values (#{testid}, #{userid}, #{reply}, #{remark}, #{questionid}, #{score})")
    int insert(StuExam stuExam);

    /**
     * 修改数据
     *
     * @param stuExam 实例对象
     * @return 影响行数
     */
    @Update("update stu_exam set score = #{score} where questionid=#{questionid} and testid=#{testid} and userid=#{userid}")
    int update(StuExam stuExam);

    /**
     * 通过主键删除数据
     *
     * @param  主键
     * @return 影响行数
     */
    int deleteById( );

    @Select("select * from stu_exam where score is null")
    List<StuExam> queryExamCheck();

    @Select("select * from stu_exam where userid= #{userId}")
    List<StuExam> queryExamByuserId(String userId);

    @Update("update stu_exam set score = #{map.score2} where questionid = #{map.questionid} and userid=#{map.studentid} and testid = #{map.testid}")
    int updateScore(@Param("map") Map<String, Object> map);

    @Select("SELECT tp.testname `testname`, SUM(tq.score) `scores`,COUNT(DISTINCT se.userid) `sum`\n" +
            "FROM test_page tp,test_questions tq,stu_exam se\n" +
            "WHERE tp.teacherid =\n" +
            "(\n" +
            "SELECT teacherid FROM teacher WHERE teacher.`REALNAME` = #{username}\n" +
            ")\n" +
            "AND tq.questionid =  tp.questionid\n" +
            "AND se.testid = tp.testid\n" +
            "AND se.questionid = tp.questionid\n" +
            "GROUP BY tp.testname;")
    List<Map<String, Object>> queryScore1(String username);

    @Select("SELECT SUM(se.score) ` totalscore`,COUNT(DISTINCT se.userid) `count` FROM stu_exam se WHERE testid =  (\n" +
            "SELECT DISTINCT tp.testid FROM test_page tp WHERE tp.testname =#{testname});\n")
    Map<String, Object> getTotalScore(String testname);

    @Select("SELECT DISTINCT tp.testname,SUM(se.score) sum FROM stu_exam se,test_page tp \n" +
            "WHERE se.userid=#{userId} AND se.testid = #{testId} AND se.`testid` = tp.`testid` \n" +
            "GROUP BY tp.`testname`")
    Map<String, Object> getExamInfo(@Param("userId") String userId, @Param("testId")String testId);

    @Select("SELECT\n" +
            "  tp.testid,\n" +
            "  tp.questionid,\n" +
            "  tq.`stem`,\n" +
            "  tq.`opta`,\n" +
            "  tq.`optb`,\n" +
            "  tq.`optc`,\n" +
            "  tq.`optd`,\n" +
            "  tq.`right`,\n" +
            "  tq.`type`,\n" +
            "  tq.`analysis`,\n" +
            "  se.reply,\n" +
            "  se.score\n" +
            "FROM\n" +
            "  stu_exam se\n" +
            "  LEFT JOIN test_page tp\n" +
            "    ON se.`testid` = tp.`testid`\n" +
            "    AND tp.`questionid` =se.`questionid`\n" +
            "  LEFT JOIN test_questions tq\n" +
            "    ON tq.`questionid` = tp.`questionid`\n" +
            "   AND tq.`questionid` = se.`questionid`\n" +
            "WHERE se.`testid` = #{testId}\n" +
            "  AND se.`userid` = #{userId}")
    List<Map<String, Object>> getExamInfo2(@Param("userId") String userId, @Param("testId")String testId);
}
